﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLite.NET;

namespace BierdopjeTV.Database
{

    class GUIEpisodes
    {
        #region Fields

        public int ShowID;
        public int EpisodeID;
        public int Episode;
        public int Season;
        public string Titel;
        public int Wachted;
        public int SubsEN;
        public int SubsNL;
        public string Summary;
        public string AirDate;
        public string Score;
        public string Stemmen;
        public string Source;
        public string Codec;
        public string FileName;
        public string Samenvatting;
        

        #endregion
    }


    static class DBEpisodes
    {

        public static List<GUIEpisodes> getEpisodes(int ShowID, int Season)
        {
            Helpers.Logger.WriteSimpel("DBEpisodes.getEpisodes()");
            List<GUIEpisodes> _BierdopjeEpisodes = new List<GUIEpisodes>();

            GUIEpisodes _Episode;
            string _Query;
            _Query = "";
            _Query += "SELECT" + Environment.NewLine;
            _Query += "	Episodes.Source," + Environment.NewLine;
            _Query += "	Episodes.Codec," + Environment.NewLine;
            _Query += "	Episodes.Path," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.ShowID," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.EpisodeID," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Titel," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Season," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Episode," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.AirDate," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Votes," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Score," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Watched," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.SubNLDownloaded AS SubNL," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.SubENDownloaded AS SubEN," + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Summary" + Environment.NewLine;
            _Query += "FROM" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes" + Environment.NewLine;
            _Query += "LEFT JOIN" + Environment.NewLine;
            _Query += "	Episodes" + Environment.NewLine;
            _Query += "	ON" + Environment.NewLine;
            _Query += "	(" + Environment.NewLine;
            _Query += "		Episodes.ShowID = BierdopjeEpisodes.ShowID" + Environment.NewLine;
            _Query += "		AND" + Environment.NewLine;
            _Query += "		Episodes.Season = BierdopjeEpisodes.Season" + Environment.NewLine;
            _Query += "		AND" + Environment.NewLine;
            _Query += "		Episodes.Episode = BierdopjeEpisodes.Episode" + Environment.NewLine;
            _Query += "	)" + Environment.NewLine;
            _Query += "WHERE" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.ShowID = " + ShowID.ToString() + Environment.NewLine;
            _Query += "	AND" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Season = " + Season.ToString() + Environment.NewLine;
            if (Database.getSettingValue("AlleenLokaleEpisodes") == "True")
            {
                _Query += "	AND" + Environment.NewLine;
                _Query += "	Downloaded = 1" + Environment.NewLine;
            }
            _Query += "ORDER BY" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes.Episode ASC" + Environment.NewLine;
            SQLiteResultSet _Result = Helpers.SqliteHelper.ExecuteQuery(_Query);

            for (int i = 0; i < _Result.Rows.Count; i++)
            {
                _Episode = new GUIEpisodes();

                _Episode.Summary = _Result.Rows[i].fields[(int)_Result.ColumnIndices["Summary"]];
                _Episode.AirDate = _Result.Rows[i].fields[(int)_Result.ColumnIndices["AirDate"]];
                _Episode.Score = _Result.Rows[i].fields[(int)_Result.ColumnIndices["Score"]];
                _Episode.Stemmen = _Result.Rows[i].fields[(int)_Result.ColumnIndices["Votes"]];
                _Episode.Source = _Result.Rows[i].fields[(int)_Result.ColumnIndices["Source"]];
                _Episode.Codec = _Result.Rows[i].fields[(int)_Result.ColumnIndices["Codec"]];
                _Episode.FileName = _Result.Rows[i].fields[(int)_Result.ColumnIndices["Path"]];
                _Episode.Samenvatting = _Result.Rows[i].fields[(int)_Result.ColumnIndices["Summary"]];
                _Episode.ShowID = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["ShowID"]]);
                _Episode.EpisodeID = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["EpisodeID"]]);
                _Episode.Season = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["Season"]]);
                _Episode.Episode = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["Episode"]]);
                _Episode.Titel = _Result.Rows[i].fields[(int)_Result.ColumnIndices["Titel"]];
                _Episode.Wachted = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["Watched"]]);
                _Episode.SubsEN = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["SubEN"]]);
                _Episode.SubsNL = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["SubNL"]]);
          
                _BierdopjeEpisodes.Add(_Episode);

                _Episode = null;

            }

            return _BierdopjeEpisodes;

        }

        public static void MarkAllWatched(int ShowID, int Season, int EpisodeID)
        {
            if (ShowID == 0)
            {
                Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET Watched = 1 WHERE EpisodeID = " + EpisodeID.ToString());
            }
            if (ShowID > 0)
            {
                if (Season > 0)
                {
                    Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET Watched = 1 WHERE ShowID  = " + ShowID.ToString() + " AND Season = " + Season.ToString());
                }
                else
                {
                    Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET Watched = 1 WHERE ShowID  = " + ShowID.ToString());
                }
            }
            
            
        }

        public static void MarkAllUNWatched(int ShowID, int Season, int EpisodeID)
        {
            if (ShowID == 0)
            {
                Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET Watched = 0 WHERE EpisodeID = " + EpisodeID.ToString());
            }
            if (ShowID > 0)
            {
                if (Season > 0)
                {
                    Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET Watched = 0 WHERE ShowID  = " + ShowID.ToString() + " AND Season = " + Season.ToString());
                }
                else
                {
                    Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET Watched = 0 WHERE ShowID  = " + ShowID.ToString());
                }
            }
        }

        public static void removeLocalEpisodes()
        {
            Helpers.Logger.WriteSimpel("removeLocalEpisodes");
            string _Query;

            _Query = "";
            _Query += "SELECT" + Environment.NewLine;
            _Query += "	DISTINCT Path" + Environment.NewLine;
            _Query += "FROM" + Environment.NewLine;
            _Query += "	Episodes" + Environment.NewLine;

            SQLiteResultSet _Result = Helpers.SqliteHelper.ExecuteQuery(_Query);
            if (_Result.Rows.Count > 0)
            {
                for (int i = 0; i < _Result.Rows.Count; i++)
                {
                    if (!System.IO.File.Exists(_Result.Rows[i].fields[0]))
                    {
                        Helpers.Logger.Write("Removing " + _Result.Rows[i].fields[0] + " from database", "removeLocalEpisodes()");
                        Helpers.SqliteHelper.ExecuteQuery("DELETE FROM Episodes WHERE Path = '" + _Result.Rows[i].fields[0] + "'");
                    }
                }
            }
            Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET Downloaded = 0");
            Helpers.SqliteHelper.ExecuteQuery("UPDATE BierdopjeEpisodes SET Downloaded = 1 WHERE EpisodeID IN (SELECT * FROM LocalEpisodeIDs)");

        }
    }
}
